1. Field of the Invention
This invention relates to database management systems and more particularly relates to binding and rebinding of query execution plans to database queries.
2. Description of the Related Art
Both critical and non-critical transactions rely on database technologies to store, retrieve, update, and delete data. Database management systems (DBMS) such as Oracle, DB2, IMS, MYSQL, are highly tuned and carefully managed to ensure that database queries to the DBMS are serviced and returned as quickly as possible. These queries may be made using various languages including SQL, XPATH, and the like. The database queries may be dynamic (meaning the query is generated as needed with a form that is unknown until they are received and likely to change between requests) or static (meaning the database query is predefined does not change form between requests, although the data values of the query may change). Static database queries lend themselves to high optimization and tuning because the form does not change.
Typically, a DBMS will store the text representing a static database query and/or assign a unique identifier for the static database query. The static database query is then associated with a query execution plan (QEP), also referred to as a query execution path. This association is referred to as a binding. The QEP identifies a set of indexes, tables, caches, and other optimization aides that the DBMS reference the QEP each time the DBMS receives a request to execute the static database query associated with the QEP. By following the QEP for a static database query a DBMS can significantly reduce response times.
A database administrator (DBA), or other user tasked with maintaining and tuning the DBMS, uses an assortment of tools and technologies to influence DB2 to generate a QEP that optimizes the use of DBMS resources and minimizes the response times for the static database query. Various tools and algorithms exist for identifying an inefficient QEP and determining a new QEP that is expected to perform better. Significant time and expense has been spent working to ensure that the new QEP is in fact more efficient than a previous QEP.
Unfortunately, these tools and algorithms are unable to provide a 100% guarantee that the new QEP for a particular database query is optimal. When a new QEP bound to a static database query in place of an original QEP performs less optimally than the original QEP, the QEP binding for the static database query has regressed. Regression of QEP bindings with its consequential reduced performance can be very costly to an organization in terms of lost reputation, failure to meet quality of service obligations, operations disruptions, time and expense in troubleshooting the matter, and the like
The problem of QEP binding regression currently unsolved due to the high number of factors which cause one DBMS environment to operate so differently from another. Some of these factors include the amount of query traffic, the variety of traffic loads, user request patterns, hardware configurations, processing bottlenecks, I/O bottlenecks, and the like. The problem of QEP binding regression is further complicated because the effects of binding a new QEP to a particular static database query may not be determinable within a predictable period of time due to usage patterns and the like. In addition, the configuration of the DBMS is such that administrative actions taken to improve performance of one set of static database queries can adversely affect another unknown set of static database queries.
Solutions exist for identifying when a QEP is regressive and for computing a new QEP that is predicted to provide more optimal performance than an original QEP. Unfortunately, these solutions still require the DBA to review the proposed new QEP and decide whether to bind the static database query to the new QEP.
The involvement of the DBA is a significant step. The DBA may need to consider bindings of new QEPs for hundreds or thousands of static database queries. The DBA must weigh the potential performance improvement against the potential that the new QEP is in fact not an improvement. The results of this decision are typically not known until the decision is made and the effects are experienced. Consequently, a DBA is often very reluctant to perform a rebinding without more proof that there will not be a regression in DBMS performance.
To minimize the impact of regression, a DBA will not perform the rebinding, or if the rebinding is performed, the DBA is prepared to quickly revert to original QEPs for a batch of static queries if performance suffers. In certain DBMS systems reverting to an original QEP is impossible. Unfortunately, this may return the status quo, but the benefit of the non-regressive new QEPs is lost at the expense of a few regressive QEPs.